<?php 
class DomSqlTestCase extends UnitTestCase {
	
	public function setUp() {
    	DomSql::init( 'localhost', 'root', '');
		$this->assertTrue( DomSql::connected() );
		
		try {DomSql::query('CREATE DATABASE `DomSqlTestDatabase`');} catch( RuntimeException $e ) {}
		
		$this->assertTrue( DomSql::selectDatabase( 'DomSqlTestDatabase' ) );
    }
    
    public function tearDown() {
		
    	try {DomSql::query('DROP DATABASE `DomSqlTestDatabase`');} catch( RuntimeException $e ) {}
    }
	
	public function testSimple() {
		$table = new DomSqlTable( 'users' );
		$this->assertFalse( $table->exists() );
		$this->assertTrue( $table->create() );
		$this->assertTrue( $table->exists() );
		
		$this->assertTrue( $table->addColumn('name','varchar(50)','index') );
		$this->assertTrue( $table->addColumn('birth', 'date') );
		$this->assertTrue( $table->addColumn('age', 'int(11)', 'index') );
		
		$this->assertTrue( DomSql::query("INSERT INTO users (name, birth, age) values ('Oliver', '1986-10-25', 21)") );
		$this->assertTrue( DomSql::query("INSERT INTO users (name, birth, age) values ('Getter', '1986-10-9', 21)") );
		
		
		$result = DomSql::selectQuery('SELECT SQL_CALC_FOUND_ROWS * FROM users');
		$this->assertIsA( $result, 'DomSqlIterator' );
		$this->assertEqual( $result->foundRows(), 2 );
		$result = $result->toArray();

		$this->assertEqual( $result[0]['name'], 'Oliver' );
		$this->assertEqual( $result[1]['name'], 'Getter' );
		
		// testing iterator
		$i = 0;
		foreach( DomSql::selectQuery('SELECT * FROM users') as $key => $res) {
			$this->assertEqual( $i, $key );
			$i++;
		}
		
		// testing iterator
		$r = DomSql::selectQuery('SELECT * FROM users');
		$r->rewind();
		$i = 0;
		while( $r->valid() ) {
			$this->assertEqual( $i, $r->key() );
			$i++;
			$r->next();
		}
		
		// testing deprecated method DomSql::getNext()
		/*DomSql::selectQuery('SELECT * FROM users');
		$i = 0;
		while( $dat = DomSql::getNext() ) {
			$i++;
		}
		$this->assertEqual( $i, 2 );
		*/
		
		$this->assertEqual( DomSql::selectOneQuery('SELECT name,id FROM users LIMIT 1'), array('name'=>'Oliver','id'=>1));
		$this->assertEqual( DomSql::selectOneQuery('SELECT name FROM users LIMIT 1', 'name'), 'Oliver');
	}
	
	public function testMultipleConnections() {
		DomSql::connect('localhost','root','','SC');
		try {DomSql::query('CREATE DATABASE `DomSqlTestDatabaseSC`', 'SC');} catch( DomSqlRuntimeException $e ) {}
		$this->assertTrue(  DomSql::selectDatabase('DomSqlTestDatabaseSC', 'SC') );
		
		$this->assertNotEqual( DomSql::getConnection('default'), DomSql::getConnection('SC') ); 
		
		$table1 = new DomSqlTable( 'aa', 'SC' );
		$table2 = new DomSqlTable( 'bb' );
		
		$this->assertFalse( $table1->exists() );
		$this->assertFalse( $table2->exists() );
		
		$this->assertTrue( $table1->create() );
		$this->assertTrue( $table2->create() );
		
		$this->assertTrue( DomSql::selectOneQuery('SHOW TABLES', 'Tables_in_domsqltestdatabasesc', 'SC'), 'aa');
		$this->assertTrue( DomSql::selectOneQuery('SHOW TABLES', 'Tables_in_domsqltestdatabase'), 'bb');

		try {DomSql::query('DROP DATABASE `DomSqlTestDatabaseSC`');} catch( DomSqlRuntimeException $e ) {}
		
		$this->assertTrue( DomSql::disconnect('SC') );
	}
	

	public function test_tableMethods() {
		$table = new DomSqlTable('testTable');
		
		$this->assertTrue( $table->create());
		
		$info = $table->getInfo();
		$this->assertEqual( $info['engine'] ,'InnoDB');
		$this->assertEqual( $info['collation'],'utf8_estonian_ci');
		
		$this->assertTrue( $table->updateEngine('MyISAM') );	
		$info = $table->getInfo();
		$this->assertEqual( $info['engine'],'MyISAM');
		
		$this->assertTrue( $table->updateCharacterSet('latin1','latin1_swedish_ci') );	
		$info = $table->getInfo();
		$this->assertEqual( $info['collation'],'latin1_swedish_ci');
		
		
		$this->assertTrue( $table->exists());
		$this->assertTrue( $table->isEmpty());
		$this->assertTrue( $table->drop());
		
	
	}
	
	public function testTableColumnMethods() {
		$table = new DomSqlTable('testTable');
		
		$this->assertTrue( $table->create());
		$this->assertTrue( $table->exists());
		
		$this->assertFalse( $table->addColumn('id', 'int(11)', false) );
		$this->assertTrue( $table->addColumn('name', 'varchar(80)', true));
		$this->assertTrue( $table->addColumn('email', 'varchar(80)', true) );
		
		$info = $table->getColumn('name');
		$this->assertEqual( $info['type'],'varchar(80)');
		$this->assertTrue( $info['null'] );
		
		$this->assertTrue( $table->columnExists('id'));
		$this->assertTrue( $table->columnExists('name'));
		$this->assertTrue( $table->columnExists('email'));
		
		$this->assertTrue( $table->changeColumn('name', 'varchar(60)', false) );
		$this->assertFalse( $table->changeColumn('name', 'varchar(60)', false) );
		
		$info = $table->getColumn('name');
		$this->assertEqual( $info['type'],'varchar(60)');
		$this->assertFalse( $info['null'] );
		
		$this->assertTrue( $table->changeColumn('newField', 'varchar(60)', true));
		$this->assertTrue( $table->columnExists('newField'));
		
		$this->assertTrue( $table->dropColumn('newField'));
		$this->assertFalse( $table->dropColumn('newField'));
		
		$this->assertTrue( $table->drop(), 'Table is dropped!');
	}
	
	public function testTableIndexMethods() {
	
		$table = new DomSqlTable('testTable');
		
		$this->assertTrue( $table->create());
		$this->assertTrue( $table->exists());

		$this->assertTrue( $table->addColumn('name', 'varchar(80)', true));
		$this->assertTrue( $table->addColumn('email', 'varchar(80)', true) );
		
		$this->assertTrue( $table->addIndex('name', array('name'), 'unique'));
		$this->assertFalse( $table->addIndex('name', array('name'), 'unique'));
		
		$info = $table->getIndex('name');
		$this->assertEqual( $info['type'],'unique');
		
		
		$this->assertTrue( $table->indexExists('name') );
		
		$this->assertTrue( $table->changeIndex('name', array('name'), 'index'));
		$info = $table->getIndex('name');
		$this->assertEqual( $info['type'] , 'index');
		
		
		$this->assertTrue( $table->dropIndex('name'));
		$this->assertFalse( $table->indexExists('name') );
		
		$this->assertTrue( $table->drop());
	}
	
	public function testTableForeignKeyMethods() {
		$table1 = new DomSqlTable('testTable1');
		$table2 = new DomSqlTable('testTable2');
		
		$this->assertTrue( $table1->create());
		$this->assertTrue( $table2->create());
		
		$this->assertTrue( $table1->addColumn('parent', 'bigint', false));
		$this->assertTrue( $table2->addColumn('child', 'bigint', false));
		
		$this->assertTrue( $table1->addForeignKey('parent', array('parent'), $table2, array('id') ));
		$this->assertTrue( $table2->addForeignKey('child', array('child'), $table1, array('id') ) );
		

		$this->assertTrue( $table1->dropForeignKey('parent') );
		$this->assertTrue( $table2->dropForeignKey('child') );
		
		$this->assertTrue( $table1->drop());
		$this->assertTrue( $table2->drop());
			
	
	}
	
	
	public function testKeyList() {
		
		DomSql::query('
			CREATE TABLE IF NOT EXISTS stuff (
				id int(11) PRIMARY KEY AUTO_INCREMENT,
				name text
			)
		');
		
		DomSql::query('INSERT INTO stuff (id,name) values (1, \'Mari\')');
		DomSql::query('INSERT INTO stuff (id,name) values (2, \'Malle\')');
		DomSql::query('INSERT INTO stuff (id,name) values (3, \'Kalle\')');
		
		
		$result = array();
		foreach( DomSql::keyListQuery('SELECT id,name FROM stuff','id','name') as $key => $item )
			$result[$key] = $item;

		$this->assertEqual( $result, array(1=>'Mari',2=>'Malle',3=>'Kalle'));
		
		$result = DomSql::keyListQuery('SELECT id,name FROM stuff','id','name')->toArray();
		$this->assertEqual( $result, array(1=>'Mari',2=>'Malle',3=>'Kalle'));
		
		$result = DomSql::keyListQuery('SELECT id,name FROM stuff','id')->toArray();
		$this->assertEqual( $result, array(1=>array('id'=>1,'name'=>'Mari'),2=>array('id'=>2,'name'=>'Malle'),3=>array('id'=>3,'name'=>'Kalle')));
		
		$result = DomSql::keyListQuery('SELECT id,name FROM stuff WHERE id = 1000','id','name')->toArray();
		$this->assertEqual( $result, array());
		
		DomSql::query('DROP TABLE stuff');
	}
	
	public function testQuery() {
		
		$table = new DomSqlTable( 'users' );
		$table->update();
		$table->addColumn('name','varchar(50)','index');
		$table->addColumn('birth', 'date');
		$table->addColumn('age', 'int(11)', 'index');
		
		DomSql::query("INSERT INTO users (name, birth, age) values ('Oliver', '1986-10-25', 21)");
		DomSql::query("INSERT INTO users (name, birth, age) values ('Getter', '1986-10-9', 21)");
		
		
		$query = new DomSqlQuery();
		$result = $query->select('name,birth')->from('users')->countFoundRows()->commit();
		$total = $result->foundRows();
		$this->assertEqual($total, 2);
		
		
		$query = new DomSqlQuery();
		$result = $query->select('name,birth')->from('users')->countFoundRows(true)->commit();
		$total = $result->foundRows();
		$this->assertEqual($total, 2);
		
		$table->drop();
	}

	public function testQuery_Where() {
		
		$q = DomSqlQuery::c()->from('t','t');
		
		$q->where('t.id = 10');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10)');
		
		$q->flushWhere()->where('t.id = 10 OR t.id = 12');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10 OR t.id = 12)');
			
		$q->flushWhere()->where('t.id = 10')->where('t.id = 12');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10 AND t.id = 12)');
		
		$q->flushWhere()->where('t.id = 10')->whereOr('t.id = 12');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10 OR t.id = 12)');
		
		$q->flushWhere()->where('t.id = 10', 'g')->whereOr('t.id = 12', 'g')->where('t.id = 13');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10 OR t.id = 12)AND (t.id = 13)');
		
		$q->flushWhere()->where('t.id = 10', 'g')->whereOr('t.id = 12', 'g')->where('t.id = 13')->where('t.id = 14');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10 OR t.id = 12)AND (t.id = 13 AND t.id = 14)');
		
		$q->flushWhere()->whereOr('t.id = 10', 'g')->whereOr('t.id = 12', 'g')->whereOr('t.id = 13')->whereOr('t.id = 14');
		$this->assertIdentical( (string)$q, 'SELECT * FROM t as t WHERE (t.id = 10 OR t.id = 12)AND (t.id = 13 OR t.id = 14)');
		
	}

	public function testIndexAndValueColumnName() {
		
		$table = new DomSqlTable( 'users' );
		$table->update();
		$table->addColumn('name','varchar(50)','index');
		$table->addColumn('birth', 'date');
		$table->addColumn('age', 'int(11)', 'index');
		
		DomSql::query("INSERT INTO users (name, birth, age) values ('Oliver', '1986-10-25', 21)");
		DomSql::query("INSERT INTO users (name, birth, age) values ('Getter', '1986-10-9', 21)");
		
		$result = DomSql::query()->from('users')->setIndexColumnName('name')->setValueColumnName('age')->commit()->toArray();
		$this->assertIdentical( $result, array('Oliver'=>'21','Getter'=>'21'));
		
		$result = DomSql::query()->from('users')->setValueColumnName('age')->commit()->toArray();
		$this->assertIdentical( $result, array(0=>'21',1=>'21'));
		
		$result = DomSql::query()->select('name,age')->from('users')->setIndexColumnName('name')->commit()->toArray();
		$this->assertIdentical( $result, array('Oliver'=>array('name'=>'Oliver','age'=>'21'),'Getter'=>array('name'=>'Getter','age'=>'21')));
		
	}

}
?>